﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using PsAutomation.axPersonel;
using PsAutomation.DataSet1TableAdapters;
using PsAutomation.Tcmb;
using Splash;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Imaging;
using System.Globalization;
using System.IO;
using System.ServiceModel;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Xml;

namespace PsAutomation
{
    public partial class Main : Form
    {
        int ProductId = 0;
        DataSet1 ds = new DataSet1();
        string tempFolder = "";
        TableAdapterManager TAM = new TableAdapterManager();
        System.Windows.Forms.Timer timer = new System.Windows.Forms.Timer();
        System.Windows.Forms.Timer timerFaceData = new System.Windows.Forms.Timer();

        ceGate.ceGateSoapClient gate = new ceGate.ceGateSoapClient();
        
        DataTable hues = new DataTable("hues");
        DataTable hues500 = new DataTable();
        P28 p28 = new P28();
        PhotosTable pt = new PhotosTable();

        karlib.klib finder = new karlib.klib();
        string WebFolder = @"c:\inetpub\wwwroot\ceOnline\Art";

        public Main()
        {
            InitializeComponent();
        }

        string cnnStr = "";
        public zkemkeeper.CZKEMClass axCZKEM1 = new zkemkeeper.CZKEMClass();
        private int iMachineNumber = 1;//the serial number of the device.After connecting the device ,this value will be changed.
        bool bIsConnected = false;

        private void MakeConnection(string IP, int Port, bool Connect)
        {
            int idwErrorCode = 0;

            if (!Connect)
            {
                axCZKEM1.Disconnect();
                bIsConnected = false;
                return;
            }

            bIsConnected = axCZKEM1.Connect_Net(IP, Port);
            if (bIsConnected == true)
            {
                iMachineNumber = 1;//In fact,when you are using the tcp/ip communication,this parameter will be ignored,that is any integer will all right.Here we use 1.
                axCZKEM1.RegEvent(iMachineNumber, 65535);//Here you can register the realtime events that you want to be triggered(the parameters 65535 means registering all)
            }
            else
            {
                axCZKEM1.GetLastError(ref idwErrorCode);
                AddMessage("Unable to connect the device, ErrorCode=" + idwErrorCode.ToString());
                //MessageBox.Show("Unable to connect the device, ErrorCode=" + idwErrorCode.ToString(), "Error");
            }
        }

        void DownloadUserInfo()
        {
            if (bIsConnected == false)
            {
                MessageBox.Show("Please connect the device first!", "Error");
                return;
            }

            string sdwEnrollNumber = "";
            string sName = "";
            string sPassword = "";
            bool bEnabled = false;

            axCZKEM1.EnableDevice(iMachineNumber, false);
            axCZKEM1.ReadAllUserID(iMachineNumber);//read all the user information to the memory
            axCZKEM1.ReadAllTemplate(iMachineNumber);//read all the users' fingerprint templates to the memory

            DataTable dtUsers = new DataTable();
            dtUsers.Columns.Add("EnrollNumber", typeof(int));
            dtUsers.Columns.Add("Name", typeof(string));

            int dwMachinePrivilege = 0;

            while (axCZKEM1.SSR_GetAllUserInfo(iMachineNumber, out sdwEnrollNumber, out sName, out sPassword, out dwMachinePrivilege, out bEnabled))
            {
                DataRow r = dtUsers.NewRow();
                r["EnrollNumber"] = int.Parse(sdwEnrollNumber)+10000;
                r["Name"] = sName;
                dtUsers.Rows.Add(r);
            }

            string xml = Serialize(dtUsers);
            ExecuteScalar("UpdKartopuUsers", xml);
        }

        void GetTcmb()
        {
            try
            {
                DataTable dtKur = new DataTable();
                dtKur.Columns.Add("Tarih", typeof(DateTime));
                dtKur.Columns.Add("Currency", typeof(string));
                dtKur.Columns.Add("Buying", typeof(decimal));
                dtKur.Columns.Add("Selling", typeof(decimal));


                Tcmb.EvdsWebServiceClient webServiceClient = new Tcmb.EvdsWebServiceClient();
                string ticket = webServiceClient.evdsLogin("evdsws", "Evds.2014");

                string[] aosSeri = {
                                    "BIE_DKDOVIZGN.DB'TP.DK.EUR.A",
	                                "BIE_DKDOVIZGN.DB'TP.DK.EUR.S",
	                                "BIE_DKDOVIZGN.DB'TP.DK.USD.A",
	                                "BIE_DKDOVIZGN.DB'TP.DK.USD.S",
	                                "BIE_DKDOVIZGN.DB'TP.DK.GBP.A",
	                                "BIE_DKDOVIZGN.DB'TP.DK.GBP.S"
                                        };

                /* ArrayOfParameter türündeki parametrede bir tarih aralığı belirtilmelidir. */
                Parameter[] aopSeri = new Parameter[2];

                
                /* İlk olarak tarih aralığını belirten “fromdate” ve “todate” paramterleri eklenir. Bu paramtere değerlerinin formatı YYYY-AA-GG şeklinde olmalıdır. */
                Parameter pFromDate = new Parameter();
                pFromDate.name = "fromdate";
                pFromDate.value = string.Format("{0:00}-{1:00}-{2:00}", DateTime.Today.AddMonths(-1).Year, DateTime.Today.AddMonths(-1).Month, DateTime.Today.AddMonths(-1).Day);   // "2016-01-01";
                aopSeri[0] = pFromDate;

                Parameter pToDate = new Parameter();
                pToDate.name = "todate";
                pFromDate.value = string.Format("{0:00}-{1:00}-{2:00}", DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day);   // "2016-01-01";
                aopSeri[1] = pToDate;

                EvdsData seriResult = webServiceClient.getEvdsData(ticket, aosSeri, aopSeri);
                foreach (Tcmb.SeriesObject obj in seriResult.Items)
                {
                    DataRow r = dtKur.NewRow();
                    r["Currency"] = obj.name;

                    foreach (DataObservation os in obj.observations)
                    {
                        DateTime ai = DateTime.Parse(os.index.Item.ToString());
                        decimal av = Decimal.Parse(os.value.Item.ToString());

                        r["Tarih"] = ai;

                        r["Buying"] = av;
                        r["Selling"] = av;
                    }
                    dtKur.Rows.Add(r);


                }

                //string lbl = seriResult.label;

                evdsLogout lo = new evdsLogout();
                webServiceClient.evdsLogout(lo);
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            }
            finally
            {
            }

        }


        private void GetFaceData_Kartopu(string IP, int Port)
        {
            Cursor = Cursors.WaitCursor;
            MakeConnection(IP, Port, true);
            DownloadUserInfo();

            if (bIsConnected == false)
            {
                MessageBox.Show("Please connect the device first", "Error");
                return;
            }

            string sdwEnrollNumber = "";
            int idwVerifyMode = 0;
            int idwInOutMode = 0;
            int idwYear = 0;
            int idwMonth = 0;
            int idwDay = 0;
            int idwHour = 0;
            int idwMinute = 0;
            int idwSecond = 0;
            int idwWorkcode = 0;
            int idwErrorCode = 0;

            DataTable dtAtt = new DataTable();
            dtAtt.Columns.Add("Id", typeof(int));
            dtAtt.Columns.Add("Tarih", typeof(DateTime));
            dtAtt.Columns.Add("Ad", typeof(string));

            axCZKEM1.EnableDevice(iMachineNumber, false);//disable the device
            if (axCZKEM1.ReadGeneralLogData(iMachineNumber))//read all the attendance records to the memory
            {
                while (axCZKEM1.SSR_GetGeneralLogData(iMachineNumber, out sdwEnrollNumber, out idwVerifyMode,
                           out idwInOutMode, out idwYear, out idwMonth, out idwDay, out idwHour, out idwMinute, out idwSecond, ref idwWorkcode))//get records from the memory
                {
                    DataRow r = dtAtt.NewRow();
                    r["Id"] = int.Parse(sdwEnrollNumber) + 10000;
                    r["Tarih"] = idwYear.ToString() + "-" + idwMonth.ToString() + "-" + idwDay.ToString() + " " + idwHour.ToString() + ":" + idwMinute.ToString() + ":" + idwSecond.ToString();
                    dtAtt.Rows.Add(r);
                }
            }
            else
            {
                Cursor = Cursors.Default;
                axCZKEM1.GetLastError(ref idwErrorCode);

                if (idwErrorCode != 0)
                {
                    AddMessage("Reading data from terminal failed, ErrorCode=" + idwErrorCode.ToString());
                    //MessageBox.Show("Reading data from terminal failed, ErrorCode: " + idwErrorCode.ToString(), "Error");
                }
                else
                {
                    AddMessage("No data from terminal returns!");
                    //MessageBox.Show("No data from terminal returns!", "Error");
                }
            }
            axCZKEM1.EnableDevice(iMachineNumber, true);//enable the device

            AddMessage("Fetched " + dtAtt.Rows.Count.ToString() + " face records. (K) "+IP.ToString());

            string xml = Serialize(dtAtt);
            ExecuteNonQuery("UpdFaceData", xml);

            ClearLog_Kartopu();

            MakeConnection(IP, Port, false);

            Cursor = Cursors.Default;
        }

        private void ClearLog_Kartopu()
        {
            if (bIsConnected == false)
            {
                AddMessage("Please connect the device first");
                return;
            }
            int idwErrorCode = 0;

            axCZKEM1.EnableDevice(iMachineNumber, false);//disable the device
            if (axCZKEM1.ClearGLog(iMachineNumber))
            {
                axCZKEM1.RefreshData(iMachineNumber);//the data in the device should be refreshed
                //MessageBox.Show("All att Logs have been cleared from terminal!", "Success");
            }
            else
            {
                axCZKEM1.GetLastError(ref idwErrorCode);
                AddMessage("Operation failed, ErrorCode=" + idwErrorCode.ToString());
                //MessageBox.Show("Operation failed, ErrorCode=" + idwErrorCode.ToString(), "Error");
            }
            axCZKEM1.EnableDevice(iMachineNumber, true);//enable the device
        }

        private void Main_Load(object sender, EventArgs e)
        {
            string key = File.ReadAllText(@"C:\ConfExpert\PSAutomation.txt");
            
            DataSet dsAuth = gate.GetCredentials(key, key, "confexpert.karbel.com", "");
            cnnStr = dsAuth.Tables["ConnectionString"].Rows[0][0].ToString();

            Database = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(cnnStr);

            /*
            GetTcmb();
            return;
            */

            btnStart.Click += btnStart_Click;

            TAM.ProductFeatureTableAdapter = new DataSet1TableAdapters.ProductFeatureTableAdapter();
            TAM.ProductFeatureTableAdapter.Connection.ConnectionString = cnnStr;

            hues.Columns.Add("ProductId", typeof(int));
            hues.Columns.Add("Hue", typeof(float));

            hues500.Columns.Add("ProductId", typeof(int));
            hues500.Columns.Add("Hue1", typeof(float));
            hues500.Columns.Add("Bri1", typeof(float));
            hues500.Columns.Add("Sat1", typeof(float));
            hues500.Columns.Add("Hue500", typeof(float));
            hues500.Columns.Add("Bri500", typeof(float));
            hues500.Columns.Add("Sat500", typeof(float));

            tempFolder = Environment.CurrentDirectory + @"\PhotosTemp";
            if (!Directory.Exists(tempFolder))
                Directory.CreateDirectory(tempFolder);

            for (int s = 1; s < 3; s++) //picture size
                for (int i = 0; i < 10; i++)    //groups of 10000photos
                    if (!Directory.Exists(WebFolder + @"\" + s.ToString() + @"\" + i.ToString()))
                        Directory.CreateDirectory(WebFolder + @"\" + s.ToString() + @"\" + i.ToString());

            timer.Interval = 15000;
            timer.Tick += new EventHandler(timer_Tick);

            timerFaceData.Interval = 3 * 60 * 60 * 1000;    //3 saatte bir
            timerFaceData.Tick += timerFaceData_Tick;

            btnStart.PerformClick();
        }

        private void SyncUsers()
        {

            axPersonel.PDKSDevicePersonCardContract[] pers = null;

            try
            {
                axPersonel.PDKSDevicePersonCardServiceClient axPersonel = new PDKSDevicePersonCardServiceClient();
                NetTcpBinding binding = (NetTcpBinding)axPersonel.Endpoint.Binding;
                binding.MaxBufferSize = Int32.MaxValue;
                binding.MaxReceivedMessageSize = Int32.MaxValue;
                binding.SendTimeout = new TimeSpan(1, 0, 0);

                axPersonel.ClientCredentials.Windows.ClientCredential.Domain = "KarbelGroup";
                axPersonel.ClientCredentials.Windows.ClientCredential.UserName = "AxAdmin";
                axPersonel.ClientCredentials.Windows.ClientCredential.Password = "Axa12533Bel";

                axPersonel.CallContext con = new axPersonel.CallContext();

                DateTime lastModifiedOn = //(DateTime)ExecuteScalar("GetMaxAxDate");
                    new DateTime(2015, 6, 14, 10, 49, 0);  //7 48 31

                //pers = axPersonel.GetPersonelCard(con, lastModifiedOn);
                pers = axPersonel.GetPersonelCardNoFilter(con);
                int recordcount = pers.Length;
            }
            catch (Exception ex)
            {
                string err = ex.ToString();
                AddMessage(err);
            }

            DataTable dtPersonel = new DataTable();
            dtPersonel.Columns.Add("Ad", typeof(string));
            dtPersonel.Columns.Add("Lokasyon", typeof(string));
            dtPersonel.Columns.Add("SicilNo", typeof(string));
            dtPersonel.Columns.Add("Sirket", typeof(string));
            dtPersonel.Columns.Add("Cinsiyet", typeof(string));
            dtPersonel.Columns.Add("Unvan", typeof(string));
            dtPersonel.Columns.Add("AmirId", typeof(string));
            dtPersonel.Columns.Add("Giris", typeof(DateTime));
            dtPersonel.Columns.Add("Cikis", typeof(DateTime));
            dtPersonel.Columns.Add("Departman", typeof(string));
            dtPersonel.Columns.Add("Email", typeof(string));
            dtPersonel.Columns.Add("Telefon", typeof(string));
            dtPersonel.Columns.Add("BeyazMavi", typeof(string));
            dtPersonel.Columns.Add("AxModified", typeof(DateTime));
            dtPersonel.Columns.Add("Tckn", typeof(string));
            dtPersonel.Columns.Add("SgkNo", typeof(string));
            dtPersonel.Columns.Add("DogumTarihi", typeof(DateTime));

            foreach (var per in pers)
                dtPersonel.Rows.Add(
                                         per.parmEmplName
                                        , per.parmBorAXDivision
                                        , per.parmBoraxEmplId
                                        , per.parmDataAreaName
                                        , per.parmGender
                                        , per.parmPositionId
                                        , per.parmReportsToWorkerEmplId
                                        , per.parmStartDate
                                        , per.parmEndDate

                                        , per.parmDepartment
                                        , per.parmEmplEmailAdress
                                        , per.parmEmplPhoneNumber
                                        , per.parmPersonelType
                                        , per.parmDateMaxFilter
                                        , per.parmEmplTCnumber
                                        , per.parmSocialInsuranceNumber
                                        , per.parmBirthDate
                                        );

            ExecQuery("SyncPersonelFromAX", Serialize(dtPersonel));
        }

        void timerFaceData_Tick(object sender, System.EventArgs e)
        {
            timerFaceData.Stop();

            SyncUsers();

            GetFaceData("10.0.0.199", 9922);    //Karbel
            
            ExecuteNonQuery("Del_Kartopu");
            GetFaceData_Kartopu("10.0.10.185", 4370);   //Kartopu.1
            GetFaceData_Kartopu("10.0.10.186", 4371);   //Kartopu.2
            ExecuteNonQuery("Merge_Kartopu");

            ExecuteNonQuery("UpdPDKS");

            timerFaceData.Start();
        }

        private void Start()
        {           
            /*
            Reprocess500();
            return;

            /*
             
             * 
             * 
             * 
            btnStart.Text = "28";
            Reprocess28();
            return;


            
            btnStart.Text = "500";
            UpdAllHues500();
            return;
            */

            btnStart.Text = "Stop";
            pnlButtonBg.BackColor = Color.Orange;

            AddMessage("starting service");

            //Hatalı feature vektörlerini sil
            ExecQuery("CleanFeatures");

            //Feature oluşturulurken tek tek yeni resimler de oluşturulduğu için gerek kalmadı.
            //WebResmiTamamla();

            UpdAllHues();

            ProcessImages();
            

            timerFaceData_Tick(null, null);
            timer.Start();
        }

        private void Stop()
        {
            btnStart.Text = "Start";
            pnlButtonBg.BackColor = Color.Transparent;

            timer.Stop();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            if (btnStart.Text.ToLower() == "stop")
                Stop();
            else
                Start();
        }

        private void timer_Tick(object sender, EventArgs e)
        {
            timer.Stop();
            ProcessImages();
            timer.Start();
            //AddMessage("Next check " + timer.Interval.ToString() + " ms. later");
        }

        private void ProcessImages()
        {
            try
            {
                while (true)
                {
                    FillMissingFeatures();

                    if (ds.xPhoto5.Rows.Count == 0)
                        break;

                    for (int i = 0; i < ds.xPhoto5.Rows.Count; i++)
                    {
                        DataSet1.xPhoto5Row p5R = ds.xPhoto5[i];
                        ProductId = p5R.ProductId;
                        this.Text = ProductId.ToString();

                        WebResmiOlustur(ProductId.ToString());

                        string fileName = tempFolder + ProductId.ToString() + ".jpg";
                        using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                        {
                            using (BinaryWriter bw = new BinaryWriter(fs))
                            {
                                bw.Write(p5R.Photo);
                                bw.Flush();
                            }
                        }

                        try
                        {
                            double[] d = ExtractFeatures(fileName);

                            if (d != null)
                            {
                                int item = 0;
                                DataSet1.ProductFeatureRow pfR = ds.ProductFeature.FindByProductId(ProductId);
                                foreach (double x in d)
                                    pfR[++item] = x;

                                using (MemoryStream ms = new MemoryStream(p5R.Photo))
                                {
                                    Bitmap img = (Bitmap)Image.FromStream(ms);
                                    if (img != null)
                                        hues.Rows.Add(ProductId, GetDominantColor(img).GetHue());
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Feature Error:" + ex, ProductId.ToString());
                        }
                    }

                    TAM.UpdateAll(ds);
                    ds.Clear();

                    UpdateHue();
                }

                this.Text = "Ready";
            }
            catch (Exception ex)
            {
                try
                {
                    AddMessage(ex.ToString());
                    Karbel.MessengerSoapClient messenger = new Karbel.MessengerSoapClient();
                    messenger.SendEmail("ce", "ce", "ConfExpert.PhotoshopService", "oaykut@gmail.com", "ConfExpert.PhotoshopService HATA !", ex.ToString());
                }
                catch
                {
                }
            }
        }

        private void FillMissingFeatures()
        {
            using (SqlConnection sqlConnection = new SqlConnection(cnnStr))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sel = new SqlCommand("GetPhotoshop4Feature", sqlConnection);
                    sel.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = (SqlCommand)sel;

                    sqlDataAdapter.TableMappings.Clear();
                    sqlDataAdapter.TableMappings.Add("Table", "xPhoto5");
                    sqlDataAdapter.TableMappings.Add("Table1", "ProductFeature");
                    sqlDataAdapter.Fill((DataSet)ds);
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        private double[] ExtractFeatures(string fileName)
        {
            try
            {
                double[] features;

                Image img = Image.FromFile(fileName);
                features = finder.ExtractFeatures(img);

                return features;
            }
            catch (Exception Ex)
            {
                AddMessage("FeatureError: " + ProductId.ToString());
            }

            return null;
        }

        private void Update28()
        {
            ExecQueryTV("Upd28", p28.Table);
            p28.Table.Clear();

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        void ExecQueryTV(string SP, params object[] p)
        {
            using (SqlConnection sqlConn = new SqlConnection(cnnStr))
            {
                sqlConn.Open();
                SqlCommand sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandText = SP;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@tbl", SqlDbType.Structured);
                parameters[0].Value = p[0];
                sqlCmd.Parameters.AddRange(parameters);
                sqlCmd.ExecuteNonQuery();
            }
        }

        private void UpdateHue()
        {
            ExecQuery("UpdHue", Serialize(hues));
            hues.Clear();

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        private void UpdateHue500()
        {
            ExecQuery("UpdHue500", Serialize(hues500));
            hues500.Clear();

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        private void UpdAllHues()
        {
            while (true)
            {
                DataTable dt = new DataTable();
                using (SqlConnection sqlConnection = new SqlConnection(cnnStr))
                {
                    sqlConnection.Open();
                    SqlCommand sel = new SqlCommand("GetAllHue", sqlConnection);
                    sel.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = (SqlCommand)sel;
                    sqlDataAdapter.Fill(dt);
                }

                if (dt.Rows.Count == 0)
                    break;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ProductId = (int)dr["ProductId"];
                    this.Text = ProductId.ToString();

                    using (MemoryStream ms = new MemoryStream((byte[])dr["Photo"]))
                    {
                        Bitmap img = (Bitmap)Image.FromStream(ms);

                        if (img != null)
                            hues.Rows.Add(ProductId, GetDominantColor(img).GetHue());
                    }
                }

                UpdateHue();
            }

            this.Text = "Ready";
        }

        private void UpdAllHues500()
        {
            while (true)
            {
                DataTable dt = new DataTable();
                using (SqlConnection sqlConnection = new SqlConnection(cnnStr))
                {
                    sqlConnection.Open();
                    SqlCommand sel = new SqlCommand("GetAllHue500", sqlConnection);
                    sel.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = (SqlCommand)sel;
                    sqlDataAdapter.Fill(dt);
                }

                if (dt.Rows.Count == 0)
                    break;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ProductId = (int)dr["ProductId"];
                    this.Text = ProductId.ToString();

                    using (MemoryStream ms500 = new MemoryStream((byte[])dr["Photo"]))
                    using (MemoryStream ms = new MemoryStream((byte[])dr["Photo1"]))
                    {
                        Bitmap img500 = (Bitmap)Image.FromStream(ms500);
                        Bitmap img = (Bitmap)Image.FromStream(ms);

                        if (img != null && img500 != null)
                        {
                            Color c500 = GetDominantColor(img500);
                            Color c = GetDominantColor(img);

                            hues500.Rows.Add(ProductId, 
                                c.GetHue(), c.GetBrightness(), c.GetSaturation(), 
                                c500.GetHue(), c500.GetBrightness(), c500.GetSaturation());
                        }
                    }
                }

                UpdateHue500();
            }

            this.Text = "Ready";
        }

        private void Reprocess28()
        {
            while (true)
            {
                DataTable dt = new DataTable();

                using (SqlConnection sqlConnection = new SqlConnection(cnnStr))
                {
                    sqlConnection.Open();
                    SqlCommand sel = new SqlCommand("Get28", sqlConnection);
                    sel.CommandType = CommandType.StoredProcedure;

                    SqlParameter[] parameters = new SqlParameter[1];
                    parameters[0] = new SqlParameter("@ProductId", SqlDbType.Int);
                    parameters[0].Value = ProductId;
                    sel.Parameters.AddRange(parameters);

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = (SqlCommand)sel;
                    sqlDataAdapter.Fill(dt);
                }

                if (dt.Rows.Count == 0)
                    break;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ProductId = (int)dr["ProductId"];
                    this.Text = ProductId.ToString();

                    DataRow r;
                    using (MemoryStream ms = new MemoryStream((byte[])dr["Photo"]))
                    {
                        Image img = Image.FromStream(ms);

                        if (img != null)
                        {
                            r = p28.AddRow(ProductId, img);
                            System.IO.File.WriteAllBytes(WebFolder + @"\1\" + (ProductId / 10000) + @"\" + ProductId + ".jpg", (byte[])r["Photo"]);
                        }
                    }
 
                }

//                Update28();
            }

            this.Text = "Ready";
        }

        private void Reprocess500()
        {
            while (true)
            {
                DataTable dt = new DataTable();

                using (SqlConnection sqlConnection = new SqlConnection(cnnStr))
                {
                    sqlConnection.Open();
                    SqlCommand sel = new SqlCommand("Get500", sqlConnection);
                    sel.CommandType = CommandType.StoredProcedure;

                    SqlParameter[] parameters = new SqlParameter[1];
                    parameters[0] = new SqlParameter("@ProductId", SqlDbType.Int);
                    parameters[0].Value = ProductId;
                    sel.Parameters.AddRange(parameters);

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                    sqlDataAdapter.SelectCommand = (SqlCommand)sel;
                    sqlDataAdapter.Fill(dt);
                }

                if (dt.Rows.Count == 0)
                    break;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ProductId = (int)dr["ProductId"];
                    this.Text = ProductId.ToString();
                    
                    Debug.WriteLine(ProductId.ToString() + ": " + ((byte[])dr["Photo"]).Length.ToString());
                    if (i % 5 == 0)
                    {
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                    }

                    DataRow r;
                    using (MemoryStream ms = new MemoryStream((byte[])dr["Photo"]))
                    {
                        Image img = Image.FromStream(ms);

                        if (img != null)
                        {
                            r = pt.AddRow(ProductId, img);
                            System.IO.File.WriteAllBytes(WebFolder + @"\1\" + (ProductId / 10000) + @"\" + ProductId + ".jpg", (byte[])r["Photo1"]);
                        }
                    }

                }

                ExecQueryTV("Upd500", pt.Table);
                pt.Table.Clear();
            }

            this.Text = "Ready";
        }


        void GetFaceData(string IP, int Port)
        {
            try
            {
                using (FaceId Client = new FaceId(IP, Port))
                {
                    Int32 DeviceCodePage = 65001;
                    DataTable dtResult = new DataTable();
                    dtResult.Columns.Add("Id", typeof(int));
                    dtResult.Columns.Add("Tarih", typeof(DateTime));
                    dtResult.Columns.Add("Ad", typeof(string));

                    String Answer;

                    string start = "2016-01-01";
                    string end = "2018-01-01";

                    string Command = @"GetRecord(start_time=""" + start + @""" end_time=""" + end + @""")";
                    FaceId_ErrorCode ErrorCode = Client.Execute(Command, out Answer, DeviceCodePage);
                    if (ErrorCode == FaceId_ErrorCode.Success)
                    {
                        String Pattern = "\\b(time=.+)\r\n";
                        MatchCollection matches = Regex.Matches(Answer, Pattern);
                        if (matches != null)
                        {
                            foreach (Match match in matches)
                            {
                                string line = match.Groups[1].Value;
                                string[] vals = line.Split(@"""".ToCharArray());

                                string[] tarih = vals[1].Split(" ".ToCharArray())[0].Split("-".ToCharArray());
                                string[] saat = vals[1].Split(" ".ToCharArray())[1].Split(":".ToCharArray());
                                DateTime date = new DateTime(int.Parse(tarih[0]), int.Parse(tarih[1]), int.Parse(tarih[2]), int.Parse(saat[0]), int.Parse(saat[1]), int.Parse(saat[2]));

                                string id = vals[3];
                                string ad = vals[5];

                                dtResult.Rows.Add(int.Parse(id), date, ad);
                            }
                        }

                        AddMessage("Fetched " + dtResult.Rows.Count.ToString() + " face records.");
                        
                        string xml = Serialize(dtResult);
                        ExecuteNonQuery("UpdFaceData", xml);

                        //TARİH ARALIĞINDA VERİ SİLMEK İÇİN;
                        //Command = @"DeleteRecord(start_time=""" + start + @""" end_time=""" + end + @""")";

                        //TÜM VERİLERİ SİLMEK İÇİN;
                        Command = @"DeleteAllRecord()";

                        ErrorCode = Client.Execute(Command, out Answer, DeviceCodePage);
                        if (ErrorCode == FaceId_ErrorCode.Success)
                        {
                            //AddMessage("Veriler cihazdan silindi !");
                        }
                        else
                        {
                            AddMessage("Veriler cihazdan silinirken hata: " + ErrorCode.ToString());
                        }
                         
                    }
                    else
                    {
                        AddMessage("Error Code: " + ErrorCode.ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                AddMessage(ex.Message);
            }
        

        }

        public static string Serialize(DataTable dataTable)
        {
            DataSet dsNew = new DataSet("MyData");
            DataTable dtNew;
            if (dataTable != null)
                dtNew = dataTable.DefaultView.ToTable();
            else
                dtNew = new DataTable();
            dtNew.TableName = "Table";
            dsNew.Tables.Add(dtNew);

            for (int i = dtNew.Columns.Count - 1; i >= 0; i--)
                if (dtNew.Columns[i].DataType == typeof(System.Byte[]))
                    dtNew.Columns.RemoveAt(i);
                else if (dtNew.Columns[i].DataType == typeof(DateTime))
                    dtNew.Columns[i].DateTimeMode = DataSetDateTime.Unspecified;

            using (TextWriter sw = new StringWriter(CultureInfo.InvariantCulture))
            using (XmlTextWriter tw = new KarbelXmlWriter(sw))
            {
                dtNew.WriteXml(tw, XmlWriteMode.IgnoreSchema);
                string oldstr = " xmlns=\"http://tempuri.org/MyData.xsd\"";
                return sw.ToString().Replace(oldstr, string.Empty); // Replace("DocumentElement", "MyData");
            }
        }

        public object ExecuteScalar(string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                //Database.ClearParameterCache();

                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    try
                    {
                        sqlConnection.Open();
                        InitCommand(sqlConnection, sqlCommand);

                        return sqlCommand.ExecuteScalar();
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
        }

        public int ExecuteNonQuery(string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    try
                    {
                        sqlConnection.Open();
                        InitCommand(sqlConnection, sqlCommand);

                        return sqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
        }

        public Database Database = null;
        private void InitCommand(DbConnection sqlConnection, DbCommand sqlCommand)
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandTimeout = 90;
            sqlCommand.Connection = sqlConnection;
        }

        public class KarbelXmlWriter : System.Xml.XmlTextWriter
        {
            public KarbelXmlWriter(System.IO.TextWriter w)
                : base(w)
            {
                //Formatting = System.Xml.Formatting.Indented;
            }

            //public override void WriteStartDocument()
            //{
            //    //to suppress
            //}
        }

        public class PhotosTable
        {
            public DataTable Table = new DataTable();

            public DataRow AddRow(int productId, Image scanned)
            {
                int ProductId;
                byte[] Photo1 = null;
                byte[] Photo500 = null;

                //2014-03-04 tarihinden itibaren kullanılmaya başlandı.
                ProductId = productId; //Önemsiz

                Photo1 = scanned
                            .CropFromCenter(224)
                            .Resize(28)
                            .Unsharpen()
                            .ToJPEG();

                Photo500 = scanned
                             .CropFromCenter(1140)
                             .Resize(500)
                             .Unsharpen()
                             .ToJPEG();

                DataRow dr = Table.Rows.Find(ProductId);

                if (dr == null)
                    dr = Table.Rows.Add(ProductId, null, Photo1, null, null, null, null, Photo500, null);
                else
                {
                    dr["Photo1"] = Photo1;
                    dr["Photo500"] = Photo500;
                }
                return dr;
            }

            public PhotosTable()
            {
                Table.Columns.Add("ProductId", typeof(int));
                Table.PrimaryKey = new DataColumn[] { Table.Columns["ProductId"] };

                Table.Columns.Add("Photo", typeof(byte[]));
                Table.Columns.Add("Photo1", typeof(byte[]));
                Table.Columns.Add("Photo2", typeof(byte[]));
                Table.Columns.Add("Photo3", typeof(byte[]));
                Table.Columns.Add("Photo4", typeof(byte[]));
                Table.Columns.Add("Photo1fs", typeof(byte[]));
                Table.Columns.Add("Photo500", typeof(byte[]));
                Table.Columns.Add("Tip", typeof(string));
            }
        }




        public class P28
        {
            public DataTable Table = new DataTable();

            public P28()
            {
                Table.Columns.Add("ProductId", typeof(int));
                Table.PrimaryKey = new DataColumn[] { Table.Columns["ProductId"] };
                Table.Columns.Add("Photo", typeof(byte[]));
                Table.Columns.Add("Tip", typeof(string));
            }

            public DataRow AddRow(int productId, Image scanned)
            {
                int ProductId;
                byte[] Photo1 = null;

                ProductId = productId;

                Photo1 = scanned
                            .CropFromCenter(100)
                            .Resize(28)
                            .Unsharpen()
                            .ToJPEG();

                DataRow dr = Table.Rows.Find(ProductId);

                if (dr == null)
                    dr = Table.Rows.Add(ProductId, Photo1);
                else
                    dr["Photo"] = Photo1;

                return dr;
            }
        }

        private void WebResmiTamamla()
        {
            List<int> resimliProductIds = new List<int>();

            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(cnnStr))
            {
                sqlConnection.Open();
                SqlCommand sel = new SqlCommand("GetProductIds", sqlConnection);
                sel.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = (SqlCommand)sel;
                sqlDataAdapter.Fill(dt);
            }

            foreach (DataRow r in dt.Rows)
                resimliProductIds.Add((int)r["ProductId"]);

            DirectoryInfo d = new DirectoryInfo(WebFolder + "\\1");
            FileInfo[] FilesArray = d.GetFiles("*.jpg", SearchOption.AllDirectories);
            List<string> dosyaliProductIds = new List<string>();
            foreach (FileInfo file in FilesArray)
                dosyaliProductIds.Add(file.Name);

            string eksikIdler = "0";
            foreach (int productId in resimliProductIds)
                if (!dosyaliProductIds.Contains(productId.ToString() + ".jpg"))
                    eksikIdler += "," + productId.ToString();

            WebResmiOlustur(eksikIdler);
        }

        private void WebResmiOlustur(string eksikIdler)
        {
            DataTable dtEksik = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(cnnStr))
            {
                SqlCommand sel = new SqlCommand("GetEksikResimler", sqlConnection);
                sel.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = (SqlCommand)sel;

                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@Mevcutlar", SqlDbType.Text);
                parameters[0].Value = eksikIdler;
                sel.Parameters.AddRange(parameters);

                sqlDataAdapter.Fill(dtEksik);
            }

            foreach (DataRow drEksik in dtEksik.Rows)
            {
                int productId = (int)drEksik["ProductId"];

                if (drEksik["Photo1"] != DBNull.Value)
                    System.IO.File.WriteAllBytes(WebFolder + @"\1\" + (productId / 10000) + @"\" + productId + ".jpg", (byte[])drEksik["Photo1"]);

                if (drEksik["Photo2"] != DBNull.Value)
                    System.IO.File.WriteAllBytes(WebFolder + @"\2\" + (productId / 10000) + @"\" + productId + ".jpg", (byte[])drEksik["Photo2"]);

                AddMessage("WebResim: " + productId);
            }
        }

        #region Standart Functions
        void ExecQuery(string SP, params object[] vals)
        {
            using (SqlConnection sqlConn = new SqlConnection(cnnStr))
            {
                sqlConn.Open();
                SqlCommand sqlCmd = sqlConn.CreateCommand();
                sqlCmd.CommandText = SP;
                sqlCmd.CommandType = CommandType.StoredProcedure;

                if (vals.Length > 0)
                {
                    SqlParameter[] parameters = new SqlParameter[1];
                    parameters[0] = new SqlParameter("@xml", SqlDbType.VarChar);
                    parameters[0].Value = vals[0];
                    sqlCmd.Parameters.AddRange(parameters);
                }
                sqlCmd.ExecuteNonQuery();
            }
        }

        private void AddMessage(string message)
        {
            txtInfo.AppendText(DateTime.Now.ToString("HH:mm:ss") + "\t" + message + Environment.NewLine);
        }

        //private string Serialize(DataTable dataTable)
        //{
        //    dataTable.TableName = "Table";

        //    foreach (DataColumn column in dataTable.Columns)
        //        if (column.DataType == typeof(DateTime))
        //            column.DateTimeMode = DataSetDateTime.Unspecified;

        //    string dsName = "MyData";
        //    if (dataTable.DataSet == null)
        //    {
        //        DataSet ds = new DataSet(dsName);
        //        ds.Tables.Add(dataTable);
        //    }
        //    else
        //        dataTable.DataSet.DataSetName = dsName;

        //    TextWriter sw = new StringWriter(CultureInfo.InvariantCulture);
        //    XmlTextWriter tw = new KarbelXmlWriter(sw);
        //    dataTable.WriteXml(tw, XmlWriteMode.IgnoreSchema);
        //    tw.Close();
        //    return sw.ToString();
        //}

        private Color GetDominantColor(Bitmap image)
        {
            if (image != null)
            {
                BitmapData bitmapData1 = image.LockBits(new Rectangle(0, 0,
                                         image.Width, image.Height),
                                         ImageLockMode.ReadOnly,
                                         PixelFormat.Format32bppArgb);

                int a = 0, r = 0, g = 0, b = 0;
                unsafe
                {
                    byte* imagePointer1 = (byte*)bitmapData1.Scan0;
                    for (int i = 0; i < bitmapData1.Height; i++)
                    {
                        for (int j = 0; j < bitmapData1.Width; j++)
                        {
                            a++;
                            b += imagePointer1[0];
                            g += imagePointer1[1];
                            r += imagePointer1[2];
                            imagePointer1 += 4;
                        }

                        //4 bytes per pixel
                        imagePointer1 += bitmapData1.Stride - (bitmapData1.Width * 4);
                    }

                }

                image.UnlockBits(bitmapData1);

                return Color.FromArgb(r / a, g / a, b / a);
            }
            return Color.Transparent;
        }
        #endregion
    }

    #region Serializer
    public class KarbelXmlWriter : System.Xml.XmlTextWriter
    {
        public KarbelXmlWriter(System.IO.TextWriter w)
            : base(w)
        {
            //Formatting = System.Xml.Formatting.Indented;
        }

        public override void WriteStartDocument()
        {
            //to suppress
        }

    #endregion
    }
}
